package cn.com.generaldata.os.service.impl;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.jsoup.helper.StringUtil;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.entity.Entity;
import org.nutz.dao.impl.sql.SqlTemplate;
import org.nutz.dao.sql.Criteria;
import org.nutz.lang.Lang;
import org.nutz.lang.Mirror;
import org.nutz.lang.Times;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.com.generaldata.jsme_indicator_system.entity.AreaPerformNz;
import cn.com.generaldata.jsme_indicator_system.entity.BiIndicatorNz;
import cn.com.generaldata.jsme_indicator_system.entity.BiShowNz;
import cn.com.generaldata.jsme_indicator_system.entity.Indicator;
import cn.com.generaldata.jsme_indicator_system.entity.IndicatorInfo;
import cn.com.generaldata.jsme_indicator_system.entity.IndustryNz;
import cn.com.generaldata.jsme_indicator_system.entity.ReportDetailNz;
import cn.com.generaldata.jsme_indicator_system.entity.ReportTypeIndicator;
import cn.com.generaldata.jsme_indicator_system.util.Constants;
import cn.com.generaldata.jsme_indicator_system.util.Constants.ReportStatSystem;
import cn.com.generaldata.os.constant.FilterConstant;
import cn.com.generaldata.os.constant.ReportConstant;
import cn.com.generaldata.os.dao.intf.BaseDAOInterface;
import cn.com.generaldata.os.paging.Filter;
import cn.com.generaldata.os.paging.FilterRule;
import cn.com.generaldata.os.pojo.AreaPerform;
import cn.com.generaldata.os.pojo.ReportDetail;
import cn.com.generaldata.os.pojo.Unit;
import cn.com.generaldata.os.service.common.AreaService;
import cn.com.generaldata.os.util.SpringContextHolder;
import cn.com.generaldata.os.vo.GISCoordinate;
import cn.com.generaldata.os.vo.GisShowDataVO;
import cn.com.generaldata.os.vo.IndicatorCategoryVO;
import cn.com.generaldata.os.vo.IndicatorQueryVO;
import cn.com.generaldata.os.vo.ReportIndicatorQueryVO;

import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

/**
 * 报表指标查询service.
 */
@Service
public class IndicatorGisService {

	private static final Logger logger = LoggerFactory.getLogger(IndicatorGisService.class);
	private final Mirror<ReportDetailNz> reportDetailMirror = Mirror.me(ReportDetailNz.class);
	private static final String AREA_ID_KEY = "areaId";
	private static final String AREA_NAME_KEY = "地区名称";
	@Autowired
	private Dao dao;
	private final SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
	@Autowired
	private SqlTemplate sqlTemplate;
	@Autowired
	private BIShowDataService biShowDataService;
	@Autowired
	private AreaService areaService;
	private BaseDAOInterface<AreaPerform> areaPerformDAO;

	public void setAreaPerformDAO(BaseDAOInterface<AreaPerform> areaPerformDAO) {
		this.areaPerformDAO = areaPerformDAO;
	}

	@Autowired
	private UnitService unitService;

	private final Map<Integer, Integer> monthQuarterMap = new HashMap<Integer, Integer>() {
		{
			put(1, 1);
			put(2, 1);
			put(3, 1);
			put(4, 2);
			put(5, 2);
			put(6, 2);
			put(7, 3);
			put(8, 3);
			put(9, 3);
			put(10, 4);
			put(11, 4);
			put(12, 4);
		}
	};

	public List<IndicatorInfo> queryIndicatorVO() {
		return dao.query(IndicatorInfo.class, null);
	}

	public List<AreaPerformNz> queryAreaVO(String areaId) {
		Criteria cri = Cnd.cri();
		if (StringUtils.isNotBlank(areaId))
			cri.where().and("parentId", "=", areaId);
		return dao.query(AreaPerformNz.class, cri);
	}

	/**
	 * 查询报表指标
	 * 
	 * @param queryVO
	 * @return
	 */
	public List<IndicatorQueryVO> queryIndicators(ReportIndicatorQueryVO queryVO) {
		List<ReportTimeVO> queryTimes = getQueryTimes(queryVO);
		String indicatorIds = queryVO.getIndicatorIds();
		String[] queryIndicatorInfos = indicatorIds.split(",");
		List<Unit> units = new ArrayList<Unit>();
		//查直属下级
		if ("4".equals(queryVO.getUnitType())) {
			List<AreaPerformNz> queryAreaVO = queryAreaVO(queryVO.getAreaId());
			for (AreaPerformNz area : queryAreaVO) {
				List<Unit> queryUnitByType = unitService.queryUnitByType(area.getId(), null);
				if (queryUnitByType != null && queryUnitByType.size() > 0) {
					for (Unit unit : queryUnitByType) {
						//是海洋局
						if ("4".equals(unit.getRole().getId()))
							units.add(unit);
					}
				}
			}
		} else
			units = unitService.queryUnitByType(queryVO.getAreaId(), queryVO.getUnitType());
		List<IndicatorQueryVO> table = new ArrayList<IndicatorQueryVO>();

		for (String indicatorId : queryIndicatorInfos) {
			Cnd cnd = Cnd.where("id", "=", indicatorId);
			IndicatorInfo indicatorInfo = dao.fetch(IndicatorInfo.class, cnd);
			for (Unit unit : units) {
				for (ReportTimeVO reportTimeVO : queryTimes) {
					IndicatorQueryVO vo = new IndicatorQueryVO();
					vo.setUnitName(unit.getUnitName());
					vo.setIndicatorName(indicatorInfo.getName());
					vo.setIndicatorUnit(indicatorInfo.getUnit());
					vo.setAreaId(unit.getArea().getId());
					String indicatorVal = null;
					try {
						indicatorVal = queryReportIndicator(unit, indicatorInfo, reportTimeVO);
					} catch (Exception e) {
						logger.error(e.getMessage(), e);
						indicatorVal = "";
					}
					vo.setReportTime(reportTimeVO.format());
					vo.setIndicatorValue(indicatorVal);
					if (!StringUtil.isBlank(indicatorVal))
						table.add(vo);
				}
			}
		}
		return table;
	}

	/**
	 * 查询报表中单个指标值.
	 * 
	 * @param unit
	 * @param indicatorInfo
	 * @param reportTimeVO
	 * @return
	 */
	public String queryReportIndicator(Unit unit, IndicatorInfo indicatorInfo, ReportTimeVO reportTimeVO) {

		Map<String, Object> params = Maps.newHashMap();
		Map<String, Object> vars = Maps.newHashMap();
		// 引用指标值不存在，默认为空字符串
		String indicatorValue = null;

		try {
			addIndicatorParam(indicatorInfo, reportTimeVO, vars, params);

			params.put("reportTimetype", reportTimeVO.timeType);
			params.put("reportYear", reportTimeVO.year);
			String sql = "SELECT rd.$clomun ";

			sql += "FROM tbl_report r INNER JOIN tbl_report_detail rd ON r.id = rd.report_id "
					+ " WHERE r.report_type_id = @reportTypeId  ";

			if (params.containsKey("row")) {
				sql += " AND rd.row_index = @row ";
			}
			// 取值时，直接取对应的地区id报表数据
			sql += " AND r.unit_id = @unitId ";
			params.put("unitId", unit.getId());

			// 汇总时间频率条件SQL
			if (reportTimeVO.timeType == Constants.REPORT_TIME_MONTH) {
				sql += " AND r.report_timetype = @reportTimetype AND r.report_year = @reportYear AND r.month = @month ";
				params.put("month", reportTimeVO.month);
			} else if (reportTimeVO.timeType == Constants.REPORT_TIME_YEAR) {
				sql += " AND r.report_timetype = @reportTimetype AND r.report_year = @reportYear AND r.report_year_time = @reportYearTime ";
				params.put("reportYearTime", reportTimeVO.yearTime);
			} else {
				sql += " AND r.report_timetype = @reportTimetype AND r.report_year = @reportYear AND r.quarter = @quarter ";
				params.put("quarter", reportTimeVO.quarter);
			}

			indicatorValue = sqlTemplate.queryForObject(sql, vars, params, String.class);
			if (indicatorValue == null) {
				indicatorValue = "";
			}
		} catch (Exception e) {
			logger.error(
					String.format("查询指标错误，引用指标信息：统计制度代码: %d,指标id：%d,vars：%s,params：%s",
							indicatorInfo.getIndicatorType(), indicatorInfo.getId(), vars, params), e);
			indicatorValue = "";
		}
		return indicatorValue;
	}

	/**
	 * 增加指标所对应的报表参数,包括报表id，所在行列
	 * 
	 * @param reference
	 * @param params
	 */
	private void addIndicatorParam(IndicatorInfo indicatorInfo, ReportTimeVO reportTimeVO, Map<String, Object> vars,
			Map<String, Object> params) {

		ReportTypeIndicator reportTypeIndicatorRel = queryReportTypeIndicatorRel(indicatorInfo.getId(), reportTimeVO);

		if (reportTypeIndicatorRel == null) {
			logger.error("无法获取指标与报表的关系，引用指标信息：统计制度代码: {},指标id：{}", indicatorInfo.getIndicatorType(),
					indicatorInfo.getId());
			throw Lang.makeThrow("无法获取指标与报表的关系");
		} else {

			String reportTypeId = reportTypeIndicatorRel.getReportTypeId();
			Integer row = reportTypeIndicatorRel.getRow();
			Integer clomun = reportTypeIndicatorRel.getTheColumn();

			params.put("reportTypeId", reportTypeId);
			if (row != null) {
				params.put("row", row);
			}
			vars.put("clomun", "f" + clomun.intValue());
		}
	}

	/**
	 * 查询报表与指标的关系.
	 * 
	 * @param reportStatSystem 报表制度
	 * @param indicatorId 指标id
	 * @param year 查询年份
	 * @return
	 */
	private ReportTypeIndicator queryReportTypeIndicatorRel(Integer indicatorId, ReportTimeVO reportTimeVO) {
		String sql = "SELECT rtip.* FROM t_report_type_indicator rtip INNER JOIN tbl_report_type rt ON rtip.report_type_id = rt.id "
				+ " INNER JOIN tbl_report_version rv "
				+ " ON rt.version_id = rv.id AND rv.start_date <= @date AND rv.end_date >= @date "
				+ " WHERE rtip.indicator_id = @indicatorId ";
		Map<String, Object> params = Maps.newHashMap();
		params.put("date", reportTimeVO.toReportVersionDate());
		params.put("indicatorId", indicatorId);
		Entity<ReportTypeIndicator> entity = dao.getEntity(ReportTypeIndicator.class);
		return sqlTemplate.queryForObject(sql, params, entity);

	}

	/**
	 * 获取两个时间范围内所有时间查询条件
	 * 
	 * @param queryVO
	 * @return
	 */
	private List<ReportTimeVO> getQueryTimes(ReportIndicatorQueryVO queryVO) {
		processReportIndicatorQueryEndTime(queryVO);

		List<ReportTimeVO> result = null;

		if (Constants.REPORT_TIME_YEAR == queryVO.getTimeType()) {
			result = getQueryYears(queryVO);
		} else if (Constants.REPORT_TIME_QUARTER == queryVO.getTimeType()) {
			result = getQueryQuarters(queryVO);
		} else {
			result = getQueryMonths(queryVO);
		}

		return result;
	}

	/**
	 * 处理查询终止时间,如果没设置终止时间，则将当前时间设置为终止时间.
	 * 
	 * @param queryVO
	 */
	private void processReportIndicatorQueryEndTime(ReportIndicatorQueryVO queryVO) {
		Calendar now = Calendar.getInstance();
		if (queryVO.getEndYear() == null || queryVO.getEndYear() == 0) {
			queryVO.setEndYear(now.get(Calendar.YEAR));
		}
		if (queryVO.getEndMonth() == null || queryVO.getEndMonth() == 0) {
			queryVO.setEndMonth(now.get(Calendar.MONTH) + 1);
		}
		if (queryVO.getEndQuarter() == null || queryVO.getEndQuarter() == 0) {
			int month = now.get(Calendar.MONTH) + 1;
			queryVO.setEndQuarter(monthQuarterMap.get(month));
		}
	}

	/**
	 * 获得查询的年份月份列表集合.
	 * 
	 * @param queryVO
	 * @return
	 */
	private List<ReportTimeVO> getQueryMonths(ReportIndicatorQueryVO queryVO) {
		Calendar now = Calendar.getInstance();

		int startYear = queryVO.getStartYear() == null ? now.get(Calendar.YEAR) : queryVO.getStartYear();
		int startMonth = queryVO.getStartMonth() == null ? now.get(Calendar.MONTH) + 1 : queryVO.getStartMonth();

		int endYear = queryVO.getEndYear() == null ? now.get(Calendar.YEAR) : queryVO.getEndYear();
		int endMonth = queryVO.getEndMonth() == null ? now.get(Calendar.MONTH) + 1 : queryVO.getEndMonth();

		List<ReportTimeVO> rts = Lists.newArrayList();
		int currentYear = startYear, currentMonth = startMonth;
		for (; currentYear <= endYear; currentYear++) {

			for (; currentMonth <= 12; currentMonth++) {
				if (currentYear == endYear && currentMonth > endMonth) {
					break;
				}
				ReportTimeVO rt = ReportTimeVO.createMonthTime(queryVO.getTimeType(), currentYear, currentMonth);
				rts.add(rt);
			}
			currentMonth = 1;
		}
		return rts;
	}

	/**
	 * 获得查询的年份季度列表集合.
	 * 
	 * @param queryVO
	 * @return
	 */
	private List<ReportTimeVO> getQueryQuarters(ReportIndicatorQueryVO queryVO) {
		Calendar now = Calendar.getInstance();
		if (queryVO.getEndYear() == null || queryVO.getEndYear() == 0) {
			queryVO.setEndYear(now.get(Calendar.YEAR));
		}
		if (queryVO.getStartYear() == null || queryVO.getStartYear() == 0) {
			queryVO.setStartYear(now.get(Calendar.YEAR));
		}
		if (queryVO.getStartQuarter() == null || queryVO.getStartQuarter() == 0) {
			int month = now.get(Calendar.MONTH) + 1;
			queryVO.setStartQuarter(monthQuarterMap.get(month));
		}

		if (queryVO.getEndQuarter() == null || queryVO.getEndQuarter() == 0) {
			int month = now.get(Calendar.MONTH) + 1;
			queryVO.setEndQuarter(monthQuarterMap.get(month));
		}
		int startYear = queryVO.getStartYear();
		int startQuarter = queryVO.getStartQuarter();

		int endYear = queryVO.getEndYear();
		int endQuarter = queryVO.getEndQuarter();

		List<ReportTimeVO> rts = Lists.newArrayList();
		int currentYear = startYear, currentQuarter = startQuarter;
		for (; currentYear <= endYear; currentYear++) {

			for (; currentQuarter <= 4; currentQuarter++) {
				if (currentYear == endYear && currentQuarter > endQuarter) {
					break;
				}
				ReportTimeVO rt = ReportTimeVO.createQuarterTime(queryVO.getTimeType(), currentYear, currentQuarter);
				rts.add(rt);
			}
			currentQuarter = 1;
		}
		return rts;
	}

	/**
	 * 获得查询的年份列表集合.
	 * 
	 * @param queryVO
	 * @return
	 */
	private List<ReportTimeVO> getQueryYears(ReportIndicatorQueryVO queryVO) {
		Calendar now = Calendar.getInstance();

		int startYear = queryVO.getStartYear() == null ? now.get(Calendar.YEAR) : queryVO.getStartYear();

		int endYear = queryVO.getEndYear() == null ? now.get(Calendar.YEAR) : queryVO.getEndYear();

		List<ReportTimeVO> rts = Lists.newArrayList();
		int currentYear = startYear;
		for (; currentYear <= endYear; currentYear++) {
			ReportTimeVO rt = ReportTimeVO.createYearTime(queryVO.getTimeType(), currentYear, queryVO.getYearTime());
			rts.add(rt);
		}
		return rts;
	}

	public List<BiIndicatorNz> queryBiIndicator() {
		return dao.query(BiIndicatorNz.class, null);
	}

	public List<GisShowDataVO> queryGisIndicator(String indicatorIds, String areaIds, String startDate, String endDate,
			String type) {
		String[] areaIdArray = new String[] {};
		if (!StringUtils.isEmpty(areaIds)) {
			if (!StringUtils.isEmpty(type)) {
				//查本级
				if (ReportConstant.OCEAN_ADMIN.equals(type))
					areaIdArray = areaIds.split(",");
				else {
					List<AreaPerformNz> areaVO = queryAreaVO(areaIds);
					List<String> areas = new ArrayList<String>();
					for (AreaPerformNz apn : areaVO)
						areas.add(apn.getId());
					areaIdArray = areas.toArray(new String[areas.size()]);
				}
			} else
				areaIdArray = areaIds.split(",");
		}
		String[] fishIdArray = new String[] {};
		if (!StringUtils.isEmpty(indicatorIds)) {
			fishIdArray = indicatorIds.split(",");
		}
		Map<String, Object> param = Maps.newHashMap();
		Date start = null;
		Date end = null;
		try {
			start = sf.parse(startDate);
			if (endDate != null)
				end = sf.parse(endDate);
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		int startYear = start.getYear() + 1900;
		int startMonth = start.getMonth() + 1;
		int endYear = start.getYear() + 1900;
		int endMonth = start.getMonth() + 1;
		if (endDate != null) {
			endYear = end.getYear() + 1990;
			endMonth = end.getMonth() + 1;
		}
		List<GisShowDataVO> result = new ArrayList<GisShowDataVO>();
		for (String areaId : areaIdArray) {
			AreaPerformNz area = dao.fetch(AreaPerformNz.class, areaId);
			if (area != null) {
				String sql = "select * from v_bi_show vf where name in (@fishName) and year>=@startYear and year<=@endYear"
						+ " and EXISTS(select id from tbl_unit tu where tu.id=vf.unit_id and tu.unit_area_id=@areaId";
				String sqlRole = " and tu.unit_role_id=" + ReportConstant.OCEAN + ") order by vf.year asc,vf.month asc";
				param.put("fishName", fishIdArray);
				param.put("areaId", areaId);
				param.put("startYear", startYear);
				param.put("endYear", endYear);
				Entity<BiShowNz> entity = dao.getEntity(BiShowNz.class);
				List<BiShowNz> list = sqlTemplate.query(sql + sqlRole, param, entity);
				if (list == null || list.size() == 0) {
					sqlRole = " and tu.unit_role_id<>" + ReportConstant.OCEAN + ") order by vf.year asc,vf.month asc";
					list = sqlTemplate.query(sql + sqlRole, param, entity);
				}
				if (list != null && list.size() > 0) {
					for (String fishName : fishIdArray) {
						Double sumOutPut = 0d;
						for (BiShowNz fpd : list) {
							if (fishName.equals(fpd.getName())) {
								Integer year = fpd.getYear();
								Integer month = fpd.getMonth();
								if (month != null) {
									//是起始年月后的
									if (startYear == year.intValue() && startMonth <= month.intValue())
										sumOutPut += fpd.getIndicatorValue() == null ? 0d : fpd.getIndicatorValue();
									else if (endYear == year.intValue() && endMonth >= month.intValue())
										sumOutPut += fpd.getIndicatorValue() == null ? 0d : fpd.getIndicatorValue();
									else if (startYear < year.intValue() && endYear > year.intValue())
										sumOutPut += fpd.getIndicatorValue() == null ? 0d : fpd.getIndicatorValue();
								}
								//年报
								else {
									sumOutPut += fpd.getIndicatorValue() == null ? 0d : fpd.getIndicatorValue();
								}
							}
						}
						GisShowDataVO vo = new GisShowDataVO();
						vo.setAreaName(area.getId());
						vo.setIndicatorName(fishName);
						vo.setIndicatorValue(sumOutPut.toString());
						result.add(vo);
					}
				} else {
					for (String fishName : fishIdArray) {
						GisShowDataVO vo = new GisShowDataVO();
						vo.setAreaName(area.getId());
						vo.setIndicatorName(fishName);
						vo.setIndicatorValue("0");
						result.add(vo);
					}
				}
			}
		}
		return result;
	}

	/**
	 * 查询指标产业
	 * @return
	 */
	public List<IndicatorCategoryVO> queryCategory() {
		String sql = "SELECT * FROM tbl_industry tin WHERE tin.code IS NOT NULL";
		List<IndustryNz> category = sqlTemplate.query(sql, null, dao.getEntity(IndustryNz.class));
		List<IndicatorCategoryVO> list = new ArrayList<IndicatorCategoryVO>();
		for (IndustryNz industry : category) {
			IndicatorCategoryVO vo = new IndicatorCategoryVO();
			vo.setId(industry.getDl());
			vo.setName(industry.getDustryName());
			list.add(vo);
		}
		return list;
	}

	public List<BiIndicatorNz> queryByCategory(String name) {
		String sql2 = "SELECT * FROM t_indicator tin WHERE EXISTS(SELECT id FROM tbl_industry tic WHERE tic.DUSTRY_NAME = @name and tin.industry_code=tic.dl)";
		String sql = "SELECT * FROM tbl_bi_indicator tbi WHERE EXISTS ( SELECT id FROM tbl_industry tic WHERE"
				+ " tic.DUSTRY_NAME = @name AND EXISTS ( SELECT id FROM t_indicator tin WHERE tin.id =tbi.indicator_id and tin.industry_code=tic.dl))";
		Map<String, Object> param = Maps.newHashMap();
		param.put("name", name);
		Entity<Indicator> entity = dao.getEntity(Indicator.class);
		List<Indicator> list = sqlTemplate.query(sql2, param, entity);
		Entity<BiIndicatorNz> entityBi = dao.getEntity(BiIndicatorNz.class);
		List<BiIndicatorNz> listBi = sqlTemplate.query(sql, param, entityBi);
		List<BiIndicatorNz> result = new ArrayList<BiIndicatorNz>();
		for (Indicator indicator : list) {
			BiIndicatorNz bn = new BiIndicatorNz();
			bn.setIndicatorId(indicator.getId());
			bn.setIndicatorName(indicator.getName());
			bn.setIndicatorShow(false);
			for (BiIndicatorNz bi : listBi) {
				if (indicator.getId().intValue() == bi.getIndicatorId().intValue()) {
					bn.setIndicatorShow(true);
					break;
				}
			}
			result.add(bn);
		}
		return result;
	}

	public void updateBiIndicator(String ids) {
		String[] idArray = ids.split(",");
		List list = Arrays.asList(idArray);
		String sql = "SELECT * FROM tbl_bi_indicator tbi";
		Entity<BiIndicatorNz> entityBi = dao.getEntity(BiIndicatorNz.class);
		List<BiIndicatorNz> listBi = sqlTemplate.query(sql, null, entityBi);
		List<String> idBi = new ArrayList<String>();
		if (listBi != null && listBi.size() > 0) {
			for (BiIndicatorNz indicator : listBi)
				idBi.add(indicator.getIndicatorId() + "");
		}
		List<String> idnew = new ArrayList<String>();
		List<String> iddelete = new ArrayList<String>();
		for (String id : idArray)
			//新的保存ID
			if (!idBi.contains(id))
				idnew.add(id);
		for (String id : idBi)
			//需要删除的ID
			if (!list.contains(id))
				iddelete.add(id);
		if (iddelete.size() > 0)
			deleteBiIndicator(iddelete);
		if (idnew.size() > 0) {
			saveBiIndicator(idnew);
			insertBiIndicator(idnew);
		}
	}

	public void saveBiIndicator(List<String> ids) {
		String sql = "SELECT * FROM t_indicator WHERE id in (@id)";
		Map<String, Object> param = Maps.newHashMap();
		param.put("id", ids.toArray());
		Entity<Indicator> entity = dao.getEntity(Indicator.class);
		List<Indicator> list = sqlTemplate.query(sql, param, entity);
		List<BiIndicatorNz> result = new ArrayList<BiIndicatorNz>();
		for (Indicator indicator : list) {
			BiIndicatorNz bn = new BiIndicatorNz();
			bn.setIndicatorId(indicator.getId());
			bn.setIndicatorName(indicator.getName());
			result.add(bn);
		}
		dao.insert(result);
	}

	public void deleteBiIndicator(List<String> ids) {
		List<BiIndicatorNz> list = queryBiIndicator(ids);
		dao.delete(list);
	}

	public List<BiIndicatorNz> queryBiIndicator(List<String> ids) {
		String sql = "select * FROM tbl_bi_indicator WHERE indicator_id in (@id)";
		Map<String, Object> param = Maps.newHashMap();
		param.put("id", ids.toArray());
		Entity<BiIndicatorNz> entity = dao.getEntity(BiIndicatorNz.class);
		List<BiIndicatorNz> list = sqlTemplate.query(sql, param, entity);
		return list;
	}

	public void insertBiIndicator(final List<String> ids) {
		new Thread("newThread") {
			@Override
			public void run() {
				BaseDAOInterface<ReportDetail> reportDetailDAO = SpringContextHolder.getBean("baseDAO");

				String sql = "SELECT treport.id FROM tbl_report treport WHERE EXISTS (SELECT id FROM t_report_type_indicator rti"
						+ " WHERE rti.report_type_id = treport.report_type_id AND rti.indicator_id IN (@ids))";
				Map<String, Object> params = ImmutableMap.<String, Object> of("ids", ids.toArray());
				List<String> reportIds = sqlTemplate.queryForList(sql, null, params, String.class);
				if (reportIds != null && reportIds.size() > 0) {
					List<BiIndicatorNz> fps = queryBiIndicator(ids);
					Filter filter = new Filter();
					filter.addRule(new FilterRule(FilterConstant.COLLECTION, "report.id", reportIds.toArray()));
					List<ReportDetail> reportDetails = reportDetailDAO.find(ReportDetail.class, filter, null);
					for (ReportDetail detail : reportDetails) {
						biShowDataService.applyReportDetailChange(detail.getReport(), detail, fps, null);
					}
				}
			}
		}.start();
	}

	public List<Map<String, String>> queryReportData(String unitName, String reportName, String year) {
		List<Map<String, String>> result = new ArrayList<Map<String, String>>();
		//查该报表的所有指标
		String sql = "select  rti.*,tid.NAME,tid.UNIT from t_report_type_indicator rti,t_indicator tid where tid.id=rti.indicator_id and EXISTS ("
				+ " SELECT id FROM tbl_report_type trt WHERE trt.type_name = @reportName AND trt.id = rti.report_type_id)";
		Map<String, Object> param = Maps.newHashMap();
		param.put("reportName", reportName);
		Entity<ReportTypeIndicator> entity = dao.getEntity(ReportTypeIndicator.class);
		List<ReportTypeIndicator> list = sqlTemplate.query(sql, param, entity);
		//取得用海企业指标在报表中的列
		String typeId = "";
		for (ReportTypeIndicator rti : list) {
			typeId = rti.getReportTypeId();
			break;
		}
		String sql2 = "select * from tbl_report_detail trd where exists(select id from tbl_report report where report.report_year=@year and report.report_type_id=@typeId and report.id=trd.report_id";
		if (StringUtils.isNotBlank(unitName)) {
			sql2 += " and exists(select id from tbl_unit tunit where tunit.id=report.unit_id and tunit.unit_name=@unitName)";
			param.put("unitName", unitName);
		}
		sql2 += ")";
		param.put("year", year);
		param.put("typeId", typeId);
		Entity<ReportDetailNz> entityDetail = dao.getEntity(ReportDetailNz.class);
		List<ReportDetailNz> detailList = sqlTemplate.query(sql2, param, entityDetail);
		result.add(getIndicatorValue(detailList, list, unitName));
		return result;
	}

	private Map<String, String> getIndicatorValue(List<ReportDetailNz> detailList, List<ReportTypeIndicator> list,
			String unitName) {
		Map<String, String> indicatorValMap = Maps.newHashMap();
		indicatorValMap.put("用海企业名称", unitName);
		for (ReportDetailNz detail : detailList) {
			//是这个用海企业的指标
			//			if (rowIndex == detail.getRowIndex()) {
			for (ReportTypeIndicator indicator : list) {
				//不是用海企业名称那个指标
				//					if (column != indicator.getTheColumn().intValue()) {
				String fieldName = "f" + indicator.getTheColumn().intValue();
				String indicatorValue = (String) reportDetailMirror.getValue(detail, fieldName);
				indicatorValMap.put(indicator.getUnit() == null ? indicator.getName() : indicator.getName() + "("
						+ indicator.getUnit() + ")", indicatorValue);
				//					}
			}
			//			}
		}
		return indicatorValMap;
	}

	public List<String> queryReporType(String year) {
		List<Map<String, String>> result = new ArrayList<Map<String, String>>();
		//查该报表的所有指标
		String sql = "select type_name from tbl_report_type trt where exists(select id from tbl_report_version trv where trv.stat_system="
				+ ReportStatSystem.USE_MARINE.getValue()
				+ " and trv.start_date<=@startDate and trv.end_date>=@startDate and trv.id=trt.version_id)";
		Map<String, Object> param = Maps.newHashMap();
		try {
			param.put("startDate", sf.parse(year + "-01-01"));
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		List<String> queryForList = sqlTemplate.queryForList(sql, null, param, String.class);
		return queryForList;
	}

	public List<GISCoordinate> turnGisCoordinate(List<Map<String, String>> list) {
		List<GISCoordinate> result = new ArrayList<GISCoordinate>();
		for (Map<String, String> map : list) {
			GISCoordinate coor = new GISCoordinate();
			String unitName = map.get("用海企业名称");
			String warrants = map.get("海域使用权证号");
			String point = map.get("界址点序号");
			String jingdu = map.get("海域使用权坐标点经度-度").replace(" ", "");
			String jingfen = map.get("海域使用权坐标点经度-分").replace(" ", "");
			String jingmiao = map.get("海域使用权坐标点经度-秒").replace(" ", "");
			String weidu = map.get("海域使用权坐标点纬度-度").replace(" ", "");
			String weifen = map.get("海域使用权坐标点纬度-分").replace(" ", "");
			String weimiao = map.get("海域使用权坐标点纬度-秒").replace(" ", "");
			//无坐标
			if (StringUtils.isBlank(jingdu) || StringUtils.isBlank(weidu))
				continue;
			coor.setUnitName(unitName);
			coor.setWarrants(warrants);
			coor.setPoint(point);

			if (jingdu.contains("°"))
				jingdu = jingdu.replace("°", "");
			if (weidu.contains("°"))
				weidu = weidu.replace("°", "");
			if (jingfen.contains("'"))
				jingfen = jingfen.replace("'", "");
			if (weifen.contains("'"))
				weifen = weifen.replace("'", "");
			if (jingmiao.contains("″") || jingmiao.contains("''"))
				jingmiao = jingmiao.replace("″", "").replace("''", "");
			if (weimiao.contains("″") || weimiao.contains("''"))
				weimiao = weimiao.replace("″", "").replace("''", "");

			try {
				Double d = 0d;

				d = Double.valueOf(jingdu) + (Double.valueOf(StringUtils.isBlank(jingfen) ? "0" : jingfen) / 60)
						+ (Double.valueOf(StringUtils.isBlank(jingmiao) ? "0" : jingmiao) / 3600);
				String jing = String.format("%.4f", d);
				coor.setLongitude(jing);
				Double w = 0d;
				w = Double.valueOf(weidu) + (Double.valueOf(StringUtils.isBlank(weifen) ? "0" : weifen) / 60)
						+ (Double.valueOf(StringUtils.isBlank(weimiao) ? "0" : weimiao) / 3600);
				String wei = String.format("%.4f", w);
				coor.setLatitude(wei);
			} catch (NumberFormatException e) {
				// TODO Auto-generated catch block
				System.out.println(warrants + "序号：" + point);
			}
			result.add(coor);
		}
		return result;
	}

	public List<GISCoordinate> arrangeCoordinate(List<GISCoordinate> list) {
		Map<String, List<GISCoordinate>> map = new HashMap<String, List<GISCoordinate>>();
		List<GISCoordinate> result = new ArrayList<GISCoordinate>();
		for (GISCoordinate gis : list) {
			String unitName = gis.getUnitName();//企业名称
			String warrants = gis.getWarrants();//海域使用权证号
			if (map.containsKey(unitName + warrants)) { //如果已经存在这个数组，就放在这里  
				List<GISCoordinate> studentList = map.get(unitName + warrants);
				studentList.add(gis);
			} else {
				List<GISCoordinate> studentList = new ArrayList<GISCoordinate>(); //重新声明一个数组list  
				studentList.add(gis);
				map.put(unitName + warrants, studentList);
			}
		}
		for (Map.Entry<String, List<GISCoordinate>> entry : map.entrySet()) {
			GISCoordinate coor = new GISCoordinate();
			List<GISCoordinate> coorList = entry.getValue();
			Collections.sort(coorList, new Comparator<GISCoordinate>() {
				@Override
				public int compare(GISCoordinate arg0, GISCoordinate arg1) {
					return arg0.getPoint().compareTo(arg1.getPoint());
				}
			});
			coor.setUnitName(coorList.get(0).getUnitName());
			coor.setWarrants(coorList.get(0).getWarrants());
			String jing = "";
			for (GISCoordinate gis : coorList) {
				String longitude = gis.getLongitude();//经度
				String latitude = gis.getLatitude();//纬度
				jing += longitude + "," + latitude + "|";
			}
			coor.setLongitude(jing);
			result.add(coor);
		}
		return result;
	}

	/**
	 * 报表时间值对象.
	 */
	public static class ReportTimeVO {
		Integer timeType;
		Integer year;
		Integer yearTime;
		Integer quarter;
		Integer month;

		public ReportTimeVO() {
		}

		public ReportTimeVO(Integer timeType, Integer year, Integer yearTime, Integer quarter, Integer month) {
			this.timeType = timeType;
			this.year = year;
			this.yearTime = yearTime;
			this.quarter = quarter;
			this.month = month;
		}

		public static ReportTimeVO create(Integer timeType, Integer year, Integer yearTime, Integer quarter,
				Integer month) {
			ReportTimeVO rtv = null;
			if (timeType == Constants.REPORT_TIME_YEAR) {
				rtv = createYearTime(timeType, year, yearTime);
			} else if (timeType == Constants.REPORT_TIME_QUARTER) {
				rtv = createQuarterTime(timeType, year, quarter);
			} else if (timeType == Constants.REPORT_TIME_MONTH) {
				rtv = createMonthTime(timeType, year, month);
			} else {
				throw Lang.makeThrow("时间类型错误，无法创建,时间类型:%d", timeType);
			}
			return rtv;
		}

		public static ReportTimeVO createYearTime(Integer timeType, Integer year, Integer yearTime) {
			return new ReportTimeVO(timeType, year, yearTime, null, null);
		}

		public static ReportTimeVO createYearTime(Integer year, Integer yearTime) {
			return new ReportTimeVO(Constants.REPORT_TIME_YEAR, year, yearTime, null, null);
		}

		public static ReportTimeVO createQuarterTime(Integer timeType, Integer year, Integer quarter) {
			return new ReportTimeVO(timeType, year, null, quarter, null);
		}

		public static ReportTimeVO createQuarterTime(Integer year, Integer quarter) {
			return new ReportTimeVO(Constants.REPORT_TIME_QUARTER, year, null, quarter, null);
		}

		public static ReportTimeVO createMonthTime(Integer timeType, Integer year, Integer month) {
			return new ReportTimeVO(timeType, year, null, null, month);
		}

		public static ReportTimeVO createMonthTime(Integer year, Integer month) {
			return new ReportTimeVO(Constants.REPORT_TIME_MONTH, year, null, null, month);
		}

		public String format() {
			String str = null;
			if (timeType == Constants.REPORT_TIME_YEAR) {
				if (yearTime == 1) {
					str = String.format("%d年 上半年", year);
				} else if (yearTime == 2) {
					str = String.format("%d年", year);
				} else {
					str = String.format("%d年 快报", year);
				}
			} else if (timeType == Constants.REPORT_TIME_QUARTER) {
				str = String.format("%d年%d季度", year, quarter);
			} else {
				str = String.format("%d年%d月", year, month);
			}
			return str;
		}

		/**
		 * 将报表报送时间转为报表版本的日期.
		 * 
		 * @return 返回报表版本日期.
		 */
		public Date toReportVersionDate() {
			Date date = null;
			if (timeType == Constants.REPORT_TIME_YEAR) {
				if (yearTime == 1) {
					date = Times.D(String.format("%d-06-30", year + 1));
				} else if (yearTime == 2) {
					date = Times.D(String.format("%d-12-31", year + 1));
				} else {
					throw Lang.makeThrow(String.format("年报类型错误，year:%d,yearTime:%d", year + 1, yearTime));
				}
			} else if (timeType == Constants.REPORT_TIME_QUARTER) {
				int quarterMonth = quarter * 3;
				date = Times.D(String.format("%d-%02d-01", year, quarterMonth));
			} else if (timeType == Constants.REPORT_TIME_MONTH) {
				date = Times.D(String.format("%d-%02d-01", year, month));
			} else {
				throw Lang.makeThrow(String.format("时间类型错误，timeType:%d", timeType));
			}
			return date;
		}

		@Override
		public String toString() {
			return "ReportTimeVO [timeType=" + timeType + ", year=" + year + ", yearTime=" + yearTime + ", quarter="
					+ quarter + ", month=" + month + "]";
		}

		public Integer getTimeType() {
			return timeType;
		}

		public void setTimeType(Integer timeType) {
			this.timeType = timeType;
		}

		public Integer getYear() {
			return year;
		}

		public void setYear(Integer year) {
			this.year = year;
		}

		public Integer getYearTime() {
			return yearTime;
		}

		public void setYearTime(Integer yearTime) {
			this.yearTime = yearTime;
		}

		public Integer getQuarter() {
			return quarter;
		}

		public void setQuarter(Integer quarter) {
			this.quarter = quarter;
		}

		public Integer getMonth() {
			return month;
		}

		public void setMonth(Integer month) {
			this.month = month;
		}

	}
}
